﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;
using ZLDC.Topo.Model.Estate;

namespace ZLDC.DAL.Estate
{
                public class CustomerDAL
    {
                                                                                public static DataTable p_customer_list_table(int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_customer_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                        public static DataTable p_customer_list_action_table(int userid, int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_customer_list_action", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                        public static DataTable p_v_changeCustomerLog_list_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_changeCustomerLog_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                        public static DataTable p_customer_MyCustomerlist(int userid, int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_Mycustomer_list_action", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }


                                                                                                                                                                                                                                                                                                                                                                                                                                        public static void p_customer_save(int Id, string customer, string customerTel, string customerClass, string occupation, string agent, string agentTel, string infoFrom, string buyingMotive, string remark, string dId, string dname, string aId, string area, string payType, string type, string Direct, int room, int room1, int hall, int restRoom, int balcony, int maxFloor, int minFloor, string houseStyle, string fitment, float minAcreage, float maxAcreage, float minPrice, float maxPrice, string priceUnit, int addUId, string AddUser, int AddDeptId, string AddDept, string owner, int ownerid, int ownerDeptId, string ownerDept, string ownerTel, int status, int isSelf, int dltFlag, string mark1, string mark2, string mark3, string modifyUser, int isValid, string tradetype, string usage, string estate, string estateid, int ischange, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@customer", customer));
            parms.Add(new SqlParameter("@customerTel", customerTel));
            parms.Add(new SqlParameter("@customerClass", customerClass));
            parms.Add(new SqlParameter("@occupation", occupation));
            parms.Add(new SqlParameter("@agent", agent));
            parms.Add(new SqlParameter("@agentTel", agentTel));
            parms.Add(new SqlParameter("@infoFrom", infoFrom));
            parms.Add(new SqlParameter("@buyingMotive", buyingMotive));
            parms.Add(new SqlParameter("@remark", remark));
            parms.Add(new SqlParameter("@dId", dId));
            parms.Add(new SqlParameter("@dname", dname));
            parms.Add(new SqlParameter("@aId", aId));
            parms.Add(new SqlParameter("@area", area));
            parms.Add(new SqlParameter("@payType", payType));
            parms.Add(new SqlParameter("@type", type));
            parms.Add(new SqlParameter("@Direct", Direct));
            parms.Add(new SqlParameter("@room", room));
            parms.Add(new SqlParameter("@room1", room1));
            parms.Add(new SqlParameter("@hall", hall));
            parms.Add(new SqlParameter("@restRoom", restRoom));
            parms.Add(new SqlParameter("@balcony", balcony));
            parms.Add(new SqlParameter("@maxFloor", maxFloor));
            parms.Add(new SqlParameter("@minFloor", minFloor));
            parms.Add(new SqlParameter("@houseStyle", houseStyle));
            parms.Add(new SqlParameter("@fitment", fitment));
            parms.Add(new SqlParameter("@minAcreage", minAcreage));
            parms.Add(new SqlParameter("@maxAcreage", maxAcreage));
            parms.Add(new SqlParameter("@minPrice", minPrice));
            parms.Add(new SqlParameter("@maxPrice", maxPrice));
            parms.Add(new SqlParameter("@priceUnit", priceUnit));
            parms.Add(new SqlParameter("@addUId", addUId));
            parms.Add(new SqlParameter("@AddUser", AddUser));
            parms.Add(new SqlParameter("@AddDeptId", AddDeptId));
            parms.Add(new SqlParameter("@AddDept", AddDept));
            parms.Add(new SqlParameter("@owner", owner));
            parms.Add(new SqlParameter("@ownerid", ownerid));
            parms.Add(new SqlParameter("@ownerDeptId", ownerDeptId));
            parms.Add(new SqlParameter("@ownerDept", ownerDept));
            parms.Add(new SqlParameter("@ownerTel", ownerTel));
            parms.Add(new SqlParameter("@status", status));
            parms.Add(new SqlParameter("@isSelf", isSelf));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@modifyUser", modifyUser));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@tardetype", tradetype));
            parms.Add(new SqlParameter("@usage", usage));
            parms.Add(new SqlParameter("@estate", estate));
            parms.Add(new SqlParameter("@estateId", estateid));
            parms.Add(new SqlParameter("@ischange", ischange));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            db.execute_procedure("p_customer_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                        public static CustomerInfo p_customer_detail(int Id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.fetch_procedure("p_customer_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            if (db.num_rows == 0)
            {
                return null;
            }
            else
            {
                CustomerInfo c = new CustomerInfo();
                c.Status = (db.rows[0]["status"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["status"].ToString());
                c.IsSelf = (db.rows[0]["isSelf"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isSelf"].ToString());
                c.DltFlag = (db.rows[0]["dltFlag"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["dltFlag"].ToString());
                c.IsValid = (db.rows[0]["isValid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isValid"].ToString());
                c.Id = (db.rows[0]["Id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["Id"].ToString());
                c.DId = (db.rows[0]["dId"] == System.DBNull.Value) ? "" : db.rows[0]["dId"].ToString();
                c.AId = (db.rows[0]["aId"] == System.DBNull.Value) ? "" : db.rows[0]["aId"].ToString();
                c.Room = (db.rows[0]["room"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["room"].ToString());
                c.Hall = (db.rows[0]["hall"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["hall"].ToString());
                c.RestRoom = (db.rows[0]["restRoom"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["restRoom"].ToString());
                c.OwnerDeptId = (db.rows[0]["ownerDeptId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["ownerDeptId"].ToString());
                c.Balcony = (db.rows[0]["balcony"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["balcony"].ToString());
                c.MaxFloor = (db.rows[0]["maxFloor"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["maxFloor"].ToString());
                c.MinFloor = (db.rows[0]["minFloor"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["minFloor"].ToString());
                c.AddUId = (db.rows[0]["addUId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["addUId"].ToString());
                c.AddDeptId = (db.rows[0]["AddDeptId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["AddDeptId"].ToString());
                c.Ownerid = (db.rows[0]["ownerid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["ownerid"].ToString());
                c.AddDate = (db.rows[0]["AddDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["AddDate"].ToString());
                c.OwnerDate = (db.rows[0]["ownerDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["ownerDate"].ToString());
                c.FollowUpDate = (db.rows[0]["followUpDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["followUpDate"].ToString());
                c.ModifyDate = (db.rows[0]["modifyDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["modifyDate"].ToString());
                c.MinAcreage = (db.rows[0]["minAcreage"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["minAcreage"].ToString());
                c.MaxAcreage = (db.rows[0]["maxAcreage"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["maxAcreage"].ToString());
                c.MinPrice = (db.rows[0]["minPrice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["minPrice"].ToString());
                c.MaxPrice = (db.rows[0]["maxPrice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["maxPrice"].ToString());
                c.Customer = (db.rows[0]["customer"] == System.DBNull.Value) ? "" : db.rows[0]["customer"].ToString();
                c.CustomerTel = (db.rows[0]["customerTel"] == System.DBNull.Value) ? "" : db.rows[0]["customerTel"].ToString();
                c.CustomerClass = (db.rows[0]["customerClass"] == System.DBNull.Value) ? "" : db.rows[0]["customerClass"].ToString();
                c.Occupation = (db.rows[0]["occupation"] == System.DBNull.Value) ? "" : db.rows[0]["occupation"].ToString();
                c.Agent = (db.rows[0]["agent"] == System.DBNull.Value) ? "" : db.rows[0]["agent"].ToString();
                c.AgentTel = (db.rows[0]["agentTel"] == System.DBNull.Value) ? "" : db.rows[0]["agentTel"].ToString();
                c.Mark2 = (db.rows[0]["mark2"] == System.DBNull.Value) ? "" : db.rows[0]["mark2"].ToString();
                c.Mark3 = (db.rows[0]["mark3"] == System.DBNull.Value) ? "" : db.rows[0]["mark3"].ToString();
                c.ModifyUser = (db.rows[0]["modifyUser"] == System.DBNull.Value) ? "" : db.rows[0]["modifyUser"].ToString();
                c.AddUser = (db.rows[0]["AddUser"] == System.DBNull.Value) ? "" : db.rows[0]["AddUser"].ToString();
                c.AddDept = (db.rows[0]["AddDept"] == System.DBNull.Value) ? "" : db.rows[0]["AddDept"].ToString();
                c.Owner = (db.rows[0]["owner"] == System.DBNull.Value) ? "" : db.rows[0]["owner"].ToString();
                c.OwnerDept = (db.rows[0]["ownerDept"] == System.DBNull.Value) ? "" : db.rows[0]["ownerDept"].ToString();
                c.OwnerTel = (db.rows[0]["ownerTel"] == System.DBNull.Value) ? "" : db.rows[0]["ownerTel"].ToString();
                c.Mark1 = (db.rows[0]["mark1"] == System.DBNull.Value) ? "" : db.rows[0]["mark1"].ToString();
                c.Type = (db.rows[0]["type"] == System.DBNull.Value) ? "" : db.rows[0]["type"].ToString();
                c.Tradetype = (db.rows[0]["tradetype"] == System.DBNull.Value) ? "" : db.rows[0]["tradetype"].ToString();
                c.Usage = (db.rows[0]["usage"] == System.DBNull.Value) ? "" : db.rows[0]["usage"].ToString();
                c.Direct = (db.rows[0]["Direct"] == System.DBNull.Value) ? "" : db.rows[0]["Direct"].ToString();
                c.HouseStyle = (db.rows[0]["houseStyle"] == System.DBNull.Value) ? "" : db.rows[0]["houseStyle"].ToString();
                c.Fitment = (db.rows[0]["fitment"] == System.DBNull.Value) ? "" : db.rows[0]["fitment"].ToString();
                c.InfoFrom = (db.rows[0]["infoFrom"] == System.DBNull.Value) ? "" : db.rows[0]["infoFrom"].ToString();
                c.BuyingMotive = (db.rows[0]["buyingMotive"] == System.DBNull.Value) ? "" : db.rows[0]["buyingMotive"].ToString();
                c.Remark = (db.rows[0]["remark"] == System.DBNull.Value) ? "" : db.rows[0]["remark"].ToString();
                c.Dname = (db.rows[0]["dname"] == System.DBNull.Value) ? "" : db.rows[0]["dname"].ToString();
                c.Area = (db.rows[0]["area"] == System.DBNull.Value) ? "" : db.rows[0]["area"].ToString();
                c.PayType = (db.rows[0]["payType"] == System.DBNull.Value) ? "" : db.rows[0]["payType"].ToString();
                c.IsChange = (db.rows[0]["isChange"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isChange"].ToString());
                return c;
            }
        }

                                                        public static DataTable p_customer_detail_table(int Id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_customer_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static int CustomerDel(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_customer set dltFlag = 1,modifydate=getdate(),modifyuser='"+_Utility.GetInfoBySession().m_sUserName+"' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                public static int CustomerChange(int id, int isself, _Database db)
        {
            int rows = 0;

            try
            {                  
                string ownerid = _Utility.GetInfoBySession().m_sUserID;
                string owner = _Utility.GetInfoBySession().m_sUserName;
                string ownerDeptid = _Utility.GetInfoBySession().m_sDeptID;
                string ownerDept = _Utility.GetInfoBySession().m_sDeptName;

                string sql = "";
                
                if(isself == 1)
                    sql = "update t_customer set isself = " + isself + " where id=" + id + "";
                else
                    sql = "update t_customer set mark1 = " + 1 + ",ownerid=" + ownerid + ",owner='" + owner + "',ownerDeptid=" + ownerDeptid + ",ownerDept='" + ownerDept + "',ownerDate='" + DateTime.Today.ToString() + "' where id=" + id + "";

                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                public static int CustomerChangeSelf(int id, int isself, _Database db)
        {
            int rows = 0;

            try
            {
                
                string sql="";
                if(isself==0)                  sql = "update t_customer set ownerDate=getDate(),isself = " + isself + ",status=0 where id=" + id + " and status !=2";
                else
                  sql = "update t_customer set ownerDate=getDate(),isself = " + isself + " where id=" + id + "";


                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                                                       
        public static void p_customer_change(int isself, int mark1, int id, int ownerid, string owner, int ownerDeptid, string ownerDept, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@isself", isself));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@ownerid", ownerid));
            parms.Add(new SqlParameter("@owner", owner));
            parms.Add(new SqlParameter("@ownerDeptid", ownerDeptid));
            parms.Add(new SqlParameter("@ownerDept", ownerDept));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_customer_change", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }


                                                                                                                                        
        public static void p_Customerfollow_save(int Id, int CustomerId, int fDeptId, string fDept, int fUserId, string fUser, string fType, string fContent, int dltFlag, int fHousingId,out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@CustomerId", CustomerId));
            parms.Add(new SqlParameter("@fDeptId", fDeptId));
            parms.Add(new SqlParameter("@fDept", fDept));
            parms.Add(new SqlParameter("@fUserId", fUserId));
            parms.Add(new SqlParameter("@fUser", fUser));
            parms.Add(new SqlParameter("@fType", fType));
            parms.Add(new SqlParameter("@fContent", fContent));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@fHousingId", fHousingId));
            parms.Add(new SqlParameter("@fDate", DateTime.Now));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_Customerfollow_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        public static void p_Customerfollow_save(int Id, int CustomerId, int fDeptId, string fDept, int fUserId, string fUser, string fType, string fContent, int dltFlag, int fHousingId,string fHousingIdList,string CustomerIdList,string paperNo,out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@CustomerId", CustomerId));
            parms.Add(new SqlParameter("@fDeptId", fDeptId));
            parms.Add(new SqlParameter("@fDept", fDept));
            parms.Add(new SqlParameter("@fUserId", fUserId));
            parms.Add(new SqlParameter("@fUser", fUser));
            parms.Add(new SqlParameter("@fType", fType));
            parms.Add(new SqlParameter("@fContent", fContent));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@fHousingId", fHousingId));
            parms.Add(new SqlParameter("@fHousingIdList", fHousingIdList));
            parms.Add(new SqlParameter("@CustomerIdList", CustomerIdList));
            parms.Add(new SqlParameter("@paparNo", paperNo));
            parms.Add(new SqlParameter("@fDate", DateTime.Now));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_Customerfollow_save1", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                                                                                                                                                        public static void p_customerAlert_save(int Id, int customerId, string AlertType,string AlertContent, int AlertScope,DateTime AlertDate ,int AddDeptId, string AddDept, int AddUserId, string AddUser, int dFlag, int dDeptId, string dDept, int dUserId, string dUser, int dltFlag, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@customerId", customerId));
            parms.Add(new SqlParameter("@AlertType", AlertType));
            parms.Add(new SqlParameter("@AlertContent", AlertContent));
            parms.Add(new SqlParameter("@AlertScope", AlertScope));
            parms.Add(new SqlParameter("@AlertDate", AlertDate));
            parms.Add(new SqlParameter("@AddDeptId", AddDeptId));
            parms.Add(new SqlParameter("@AddDept", AddDept));
            parms.Add(new SqlParameter("@AddUserId", AddUserId));
            parms.Add(new SqlParameter("@AddUser", AddUser));
            parms.Add(new SqlParameter("@dFlag", dFlag));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_customerAlert_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                                                public static DataTable p_Customerfollow_list_table(int page, int pagesize, int customerId,int deptid ,ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@customerId", customerId));
            parms.Add(new SqlParameter("@deptid", deptid));
            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_Customerfollow_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_Customerfollow_list_top50_table(int page, int pagesize, int customerId, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@customerId", customerId));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_Customerfollow_list_top50", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                public static DataTable p_customerAlert_list_table(int page, int pagesize, int CustomerId, int userid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@CustomerId", CustomerId));
            parms.Add(new SqlParameter("@userid", userid));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_customerAlert_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                public static void p_CustomerAlert_update(int Id, int dFlag, int dDeptId, string dDept, int dUserId, string dUser, out int retval, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@dFlag", dFlag));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_CustomerAlert_update", parms);


            retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                                                                public static DataTable p_CustomerfollowReport_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_CustomerfollowReport_list1", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                public static DataTable lp_CustomerfollowReport_list_table(int page, int pagesize, string condition, string condition_1, ref int total, _Database db, string groupbyfiled, string showfiled, string @orderbyfiled, string depttype)
        {
            
                                                                                                                                                            
                                    DataSet ds=new DataSet();
            if (depttype == "1")
            {
                ds = db.Query(" select eid,ename,COUNT(ename) as count,(select count(eid) from V_lp_Followall "+condition_1+"  and eid=v.eid ) as allcount from V_lp_Followall as v  " + condition + " group by ename,eid order by count desc");
            }
            else
            {
                ds = db.Query(" select eid,ename,COUNT(ename) as count from V_lp_Followall " + condition + " group by ename,eid order by count desc");
            }
            
            
            return ds.Tables[0];
        }


                                                                                public static DataTable lp_salecount( string lp_salecountsql, _Database db)
        {
            
                                                                                                                                                            
                                    DataSet ds;
            ds = db.Query(lp_salecountsql);
            
            
            return ds.Tables[0];
        }

                                                        public static void p_Customerfollow_delete(int Id, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_Customerfollow_delete", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                                                                public static DataTable p_customerAlertReport_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_customerAlertReport_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                                                                                public static DataTable p_Customer_Follow_Report_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_Customer_Follow_Report_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                public static DataSet  SelectCustomersById(int id,string FiledList, _Database db)
        {
           
            DataSet ds = null;
            try
            {
                string sql = "select "+FiledList+" from t_customer  where id=" + id + "";
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

        public static DataSet SelectCustomers(string condition, string fileds, string orderby, _Database db)
        {
            DataSet ds = null;
            try
            {
                                string sql = "select " + fileds + " from t_customer  " + condition ;
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

                public static int UpdateStatus(string cid, int deptid_from, string deptname_from, int userid_from, string username_from, string flag, int userid_to, int deptid_to,string deptname_to,string username_to, _Database db) 
        {
            int rows = 0;

            try
            {
                string sql = "";
                string sqlstr = "";
                string IdList = "";

                if (flag == "true")
                {
                    sqlstr = "SELECT [idlist]=STUFF((SELECT ','+cast([id] as varchar) FROM t_customer t where dltFlag=0 and ownerid =" + userid_from + " FOR XML PATH('')), 1, 1, '')";
                    sql = "begin tran;update t_customer set ownerid=" + userid_to + ",owner='" + username_to + "',ownerDeptId=" + deptid_to + ",ownerDept='" + deptname_to + "' where dltFlag=0 and ownerid=" + userid_from + "; if @@error <> 0 begin rollback;RAISERROR ('数据库错误.', 16, 1) end else begin commit; end;";

                    DataSet ds = null;

                    if (sqlstr != "")
                    {
                        ds = db.Query(sqlstr);

                        db.Connection.Open();

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            IdList = ds.Tables[0].Rows[0][0].ToString();
                        }
                    }
                    if (IdList == "")
                    {
                        IdList = cid;
                    }
                    rows = db.ExecuteSql(sql);
                    if (rows > 0)
                    {
                        string outmsg = "";
                        int newid = 0;
                        int user_id = _Convert.ToInt(_Utility.GetInfoBySession().m_sUserID);                        string user_name = _Utility.GetInfoBySession().m_sUserName;                        int user_from = userid_from;                        string user_from_name = username_from;                        int user_to = userid_to;                        string user_to_name = username_to;                        string opr_type = "转客";                        string sqlText = sql + "detail:(" + IdList + ")";
                        ZLDC.DAL.Estate.TranLogDAL.p_tran_log_insert(user_id, user_name, user_from, user_from_name, user_to, user_to_name, opr_type, sqlText, out outmsg, out newid, db);


                    }
                    ds = null;
                    sqlstr = "SELECT [idlist]=STUFF((SELECT ','+cast([id] as varchar) FROM t_customer t where dltFlag=0 and addUId="+userid_from+" and isChange>0 FOR XML PATH('')), 1, 1, '')";
                    sql = "begin tran;update t_customer set addUId=" + userid_to + ",AddUser='" + username_to + "',AddDeptId=" + deptid_to + ",AddDept='" + deptname_to + "' where dltFlag=0 and addUId=" + userid_from + " and isChange>0; if @@error <> 0 begin rollback;RAISERROR ('数据库错误.', 16, 1) end else begin commit; end;";

                    if (sqlstr != "")
                    {
                        ds = db.Query(sqlstr);

                        db.Connection.Open();

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            IdList = ds.Tables[0].Rows[0][0].ToString();
                        }
                    }
                    if (IdList == "")
                    {
                        IdList = cid;
                    }
                    rows = db.ExecuteSql(sql);
                    if (rows > 0)
                    {
                        string outmsg = "";
                        int newid = 0;
                        int user_id = _Convert.ToInt(_Utility.GetInfoBySession().m_sUserID);                        string user_name = _Utility.GetInfoBySession().m_sUserName;                        int user_from = userid_from;                        string user_from_name = username_from;                        int user_to = userid_to;                        string user_to_name = username_to;                        string opr_type = "转客";                        string sqlText = sql + "detail:(" + IdList + ")";
                        ZLDC.DAL.Estate.TranLogDAL.p_tran_log_insert(user_id, user_name, user_from, user_from_name, user_to, user_to_name, opr_type, sqlText, out outmsg, out newid, db);

                    }
                
                }
                else
                {

                    sql = "begin tran;"+
                          "update t_customer set ownerid=" + userid_to + ",owner='" + username_to + "',ownerDeptId=" + deptid_to + ",ownerDept='" + deptname_to + "' where Id in(" + cid + ") and dltFlag=0  and ownerid="+userid_from+" ; " +
                          "update t_customer set addUId=" + userid_to + ",AddUser='" + username_to + "',AddDeptId=" + deptid_to + ",AddDept='" + deptname_to + "' where  Id in (" + cid + ") and dltFlag=0 and addUId="+userid_from+" and isChange>0 ;" +
                          "if @@error <> 0 begin rollback;RAISERROR ('数据库错误.', 16, 1) end else begin commit; end;";
                    if (IdList == "")
                    {
                        IdList = cid;
                    }
                    rows = db.ExecuteSql(sql);
                    if (rows > 0)
                    {
                        string outmsg = "";
                        int newid = 0;
                        int user_id = _Convert.ToInt(_Utility.GetInfoBySession().m_sUserID);                        string user_name = _Utility.GetInfoBySession().m_sUserName;                        int user_from = userid_from;                        string user_from_name = username_from;                        int user_to = userid_to;                        string user_to_name = username_to;                        string opr_type = "转客";                        string sqlText = sql + "detail:(" + IdList + ")";
                        ZLDC.DAL.Estate.TranLogDAL.p_tran_log_insert(user_id, user_name, user_from, user_from_name, user_to, user_to_name, opr_type, sqlText, out outmsg, out newid, db);

                    }

                }

                
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                public static int OpenStatus(int id, int typeid, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "";
                if(typeid == 1)
                    sql += "update t_customer set isSelf = 0,status = 0 where Id = " + id.ToString();
                else if(typeid == 2)
                    sql += "update t_customer set isSelf = 1,status = 0 where Id = " + id.ToString();
               
                rows = db.ExecuteSql(sql);                          
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                        public static int DeleteCustomer (int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "delete from t_customer where  id =" + id + " and dltflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                                                        public static int DeleteWeekDaysCustomer(_Database db)
        {
            int rows = 0;

            try
            {
                string sql = "delete from t_customer where  dltflag=1 and modifyDate  > DATEADD(dd,-7,GETDATE()) ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                public static int UpdateDltFlag(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update  t_customer set dltflag=0  where  id =" + id + " and dltflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                public static DataSet  SelectHousingOrCustomerCount(string id,_Database db) 
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = "select  count(*) as num from t_housing where markuid=" + id + " and dltflag=0 ;select count(*) as num from t_customer where ownerid='" + id + "' and dltflag=0";
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

                                                public static DataTable GetWillToPublic(string condition, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "select id from t_customer";
                strSql += condition;
                DataSet ds = db.Query(strSql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }            
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                        public static DataTable GetCustTelList(string CustTel, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string sSql = "select ownerdept,convert(varchar(10),ownerDate,121) ownerDate from t_customer where IsSelf='1' and ownerdeptid=" + _Utility.GetInfoBySession().m_sDeptID + " and  customerTel='" + CustTel + "' and dltflag=0 order by ownerDate ";
                DataSet ds = db.Query(sSql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                public static DataSet SelectIsSelfById(int id,_Database db) 
        {
            DataSet ds = null;

            try
            {
                string sql = "select * From t_customer where id ='" + id + "' ";
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

                                                                public static string CustomerToDatum(int id,int status, _Database db) 
        {
            string outmsg = "";
            try
            {
                int rows = 0;
                string sql = "update t_customer set status=" + status + "  where id=" + id + " ";
                rows = db.ExecuteSql(sql);
                if (rows > 0)
                {
                    outmsg = "操作成功";
                }
                else 
                {
                    outmsg = "操作失败";
                }
            }
            catch
            {
                outmsg = "操作失败";
            }
            finally 
            {
                db.close();
            }
            return outmsg;
        }

                                                                                        public static string ManagerChangeIsSelf(int id,int ownerid,string owner,int ownerdeptid,string ownerdept, _Database db)
        {
            string outmsg = "";
            try
            {
                int rows = 0;
                string sSql = " update t_customer set isself=0,ischange=0,status=0, owner='"+owner+"',ownerid="+ownerid+",ownerDept='"+ownerdept+"',ownerDeptId="+ownerdeptid+",ownerDate=getDate() where Id="+id+" ";
                rows = db.ExecuteSql(sSql);
                if (rows > 0)
                {
                    outmsg = "操作成功！";
                }
                else 
                {
                    outmsg = "操作失败,请稍候再试!";
                }
            }
            catch
            {
                outmsg = "操作失败,请稍候再试!";
            }
            finally 
            {
                db.close();
            }
            return outmsg;

        }


                                                              public static string CustomerToTransactions(int id,_Database db) 
        {
            string outmsg = "";
            try
            {
                int rows = 0;
                string sql = " update t_customer set status=2 where id="+id+"";
                rows=db.ExecuteSql(sql);
                if (rows > 0)
                {
                    outmsg = "操作成功";
                }
                else 
                {
                    outmsg = "操作失败,请稍候再试！";
                }
            }
            catch
            {
                outmsg = "操作失败,请稍候再试！";
            }
            finally 
            {
                db.close();
            }
            return outmsg;
        }

        public static string DeleteCustomerAlert(int id, _Database db) 
        {
            string outmsg = "";
           
            try
            {
                string sql = " update t_customerAlert set dltflag=1 where id=" + id + " ";
                int rows = db.ExecuteSql(sql);
                if (rows > 0)
                {
                    outmsg = "删除成功";
                }
                else 
                {
                    outmsg = "删除失败";
                }
            }
            catch
            {
                outmsg = "删除失败";
            }
            finally 
            {
                db.close();
            }
            return outmsg;
        }

                public static void UpdateTranRemark(int id,string tranRemark,_Database db) 
        {
            try
            {
                string sql = " update t_customer set mark3='" + tranRemark + "'  where id=" + id + " ";
                db.ExecuteSql(sql);
            }
            catch
            {

            }
            finally 
            {
                db.close();
            }
        }

                public static int ChangeCustomerCount(int deptid, string Tel, _Database db)
        {
            int count = 0;
            try
            {
                string sql = "select COUNT(*) from t_customer where ownerDeptId=" + deptid + " and CustomerTel='" + Tel + "' and dltflag=0 ";
                DataSet ds = db.Query(sql);
                count = _Convert.ToInt(ds.Tables[0].Rows[0][0].ToString());
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
            return count;
        }


        public static int ChangeCustomerCountByUserId(int UserId, string Tel, _Database db) 
        {
            int count = 0;
            try
            {
                string sql = "select COUNT(*) from t_customer where ownerId=" + UserId + " and CustomerTel='" + Tel + "' and dltflag=0 ";
                DataSet ds = db.Query(sql);
                count = _Convert.ToInt(ds.Tables[0].Rows[0][0].ToString());
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
            return count;
        }

                                                        public static int CustomerFollowByTypeOrDate(int CustomerId, _Database db) 
        {
            int count = 0;
            try
            {
                string Sql = "select count(*) from t_Customerfollow where fType in ('带看售房','带看租房') and CustomerId=" + CustomerId + "   and fuserid="+_Utility.GetInfoBySession().m_sUserID+" and DATEDIFF(DAY,fDate,getDate())=0 ";
                DataSet ds = db.Query(Sql);
                
                count =_Convert.ToInt(ds.Tables[0].Rows[0][0].ToString());
            }
            catch
            {

            }
            finally 
            {
                db.close();
            }
            return count;
        }


                                                        public static DataTable ShowOtherCustomer(int id, _Database db)
        {
            DataSet ds = null;
            DataTable dt = null;
            try
            {
                string sql = "select  * from t_customer where dltflag = 0 and customerTel = (select customerTel from t_customer where Id = " + id + ") and Id != " + id;
                                 string SalesManagerStation = _Utility.GetWebConfigSetting("SalesManagerStation");
                if (_Utility.GetInfoBySession().m_sRoleID != "1")                {
                    if (SalesManagerStation.Contains(_Utility.GetInfoBySession().m_sStation))
                        sql += " and ownerDeptId=" + _Utility.GetInfoBySession().m_sDeptID + "";
                    else
                        sql += " and ownerid=" + _Utility.GetInfoBySession().m_sUserID + "";
                }
                ds = db.Query(sql);

                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                public static void UpdateLookCustomerCount(int id, _Database db) 
        {
            
            try
            {
                string sql = "update t_customer set mark2=(select top 1 COUNT(*) from (" +
                            " select * from t_Customerfollow  where CustomerId="+ id +"" +
                            " union" +
                            " select * from t_Customerfollow_histroy where CustomerId="+ id +"" +
                            " )as a  where a.fHousingId>0 group by  fHousingId order by COUNT(*) desc" +
                            " ) where id="+ id +"";
                
                db.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally 
            {
                db.close();
            }
           
        }

        public static void p_Customerfollow_housingMore_save(int customerFollowId, int housingId, string housingName, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@customerFollowId", customerFollowId));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@housingName", housingName));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_Customerfollow_housingMore_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        public static void p_Customerfollow_customerMore_save(int customerFollowId, int teamworkUserId, string teamworkUserName,int teamworkDeptId, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@customerFollowId", customerFollowId));
            parms.Add(new SqlParameter("@teamworkUserId", teamworkUserId));
            parms.Add(new SqlParameter("@teamworkUserName", teamworkUserName));
            parms.Add(new SqlParameter("@teamworkDeptId", teamworkDeptId));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_Customerfollow_customerMore_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        
                                                        public static void UpdateChangeCustomerClasss(string customerClass,int Id, _Database db)
        {

            try
            {
                string sql = " update t_customer set customerClass='" + customerClass + "' where id=" + Id + "";
                db.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.close();
            }

        }
    }
}   
